package dao;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Vector;

import util.Propiedades;
import dominio.Articulo;
import dominio.Marca;
import dominio.Objeto;
import dominio.Vehiculo;

public class BdDao implements daoInterface {

	private Connection connection;
	private Properties propiedades;

	public BdDao() {
		SQLiteConexion();
	}
	
	//Metodo que crea una conexion con la BD.
    private void SQLiteConexion() {
    	try{
    		propiedades = new Properties();
//    		propiedades.load(new FileInputStream("/home/insur/Escritorio/Lista de Precios/configuration.properties"));
			propiedades = Propiedades.obtenerPropiedades();
			String url = propiedades.getProperty("repositorio_local");
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:" + url);
         
    	} catch (FileNotFoundException e) {
	    	System.out.println("Error! El archivo no exite.");
		} catch(Exception e){
			e.printStackTrace();
		}

    }
    
	public Vector<Marca> listarMarcas() {
		Vector<Marca> marcas = new Vector<Marca>();
		String query = "SELECT * FROM marca;";
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(query);
			while (result.next()) {
				Marca marca = new Marca(result.getInt("id_marca"), 
							  new String(result.getString("nombre")));
				marcas.add(marca);
			}
			result.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return marcas;		
	}
	
	public Vector<Vehiculo> listarVehiculos(Integer id_marca) {
		Vector<Vehiculo> vehiculos = new Vector<Vehiculo>();
		String query = "SELECT * FROM vehiculo WHERE id_marca = " + id_marca.intValue() + ";";
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(query);
			while (result.next()) {
				Vehiculo vehiculo = new Vehiculo(result.getInt("id_vehiculo"), 
									new String(result.getString("nombre")));
				vehiculos.add(vehiculo);
			}
			result.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return vehiculos;		
	}
	
	public Vector<Articulo> listarArticulos(Integer id_vehiculo) {
		Vector<Articulo> articulos = new Vector<Articulo>();
		String query = "SELECT A.id_articulo AS id_articulo, A.rubro_marca AS rubro_marca, " +
				"A.codigo AS codigo, A.medidas AS medidas, A.bocas AS bocas, A.detalle AS detalle, " +
				"A.precio_lista AS precio_lista FROM articulo AS A JOIN articulo_vehiculo AS AV " +
				"ON A.id_articulo=AV.id_articulo WHERE AV.id_vehiculo = " + id_vehiculo.intValue() + ";";
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(query);
			while (result.next()) {
				Articulo articulo = new Articulo(result.getInt("id_articulo"), 
									new String(result.getString("rubro_marca")), 
									new String(result.getString("codigo")),
									new String(result.getString("medidas")),
									new String(result.getString("bocas")), 
									new String(result.getString("detalle")),
									result.getFloat("precio_lista"));
				articulos.add(articulo);
			}
			result.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return articulos;
	}
	
	public Vector<Objeto> listarTapaCilindro() {
		Vector<Objeto> objetos = new Vector<Objeto>();
		String query = "SELECT * FROM tapa_cilindro;";
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(query);
			while (result.next()) {
				Objeto objeto = new Objeto(new Integer(result.getInt("id_tapa")), new String(result.getString("codigo")),
											new String(result.getString("detalle")), new Float(result.getFloat("precio")));
				objetos.add(objeto);
			}
			result.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return objetos;		
	}
	
	public Vector<Objeto> listarBlockMotor() {
		Vector<Objeto> objetos = new Vector<Objeto>();
		String query = "SELECT * FROM block_motor;";
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(query);
			while (result.next()) {
				Objeto objeto = new Objeto(new Integer(result.getInt("id_block")), new String(result.getString("codigo")),
											new String(result.getString("detalle")), new Float(result.getFloat("precio")));
				objetos.add(objeto);
			}
			result.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return objetos;		
	}
	
	public String ultimaVersion() {
		String ultima_version = new String("");
	
		String query = "SELECT ultima_fecha_hora FROM configuracion;";
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(query);
			ultima_version = result.getString("ultima_fecha_hora");
			result.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ultima_version;
	}
	
	public void actualizarPreciosTapaCilindro(Vector<Objeto> objetos) {
		try {
			connection.setAutoCommit(false);
			Statement statement = connection.createStatement();
			
			for(int i=0; i < objetos.size(); i++) {
				Objeto fila = objetos.elementAt(i);
				String query = "UPDATE tapa_cilindro SET precio = " + fila.getPrecio() + 
								" WHERE id_tapa = " + fila.getId_tapa() + ";";
				statement.executeUpdate(query);
			}
			connection.commit();
			statement.close();
			connection.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void actualizarPreciosBlockMotor(Vector<Objeto> objetos) {
		try {
			connection.setAutoCommit(false);
			Statement statement = connection.createStatement();
			
			for(int i=0; i < objetos.size(); i++) {
				Objeto fila = objetos.elementAt(i);
				String query = "UPDATE block_motor SET precio = " + fila.getPrecio() + 
								" WHERE id_block = " + fila.getId_tapa() + ";";
				statement.executeUpdate(query);
			}
			connection.commit();
			statement.close();
			connection.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void resetDataBase() {
		try {
			connection.setAutoCommit(false);
				
    		propiedades = new Properties();
//    		propiedades.load(new FileInputStream("/home/insur/Escritorio/Lista de Precios/configuration.properties"));
			propiedades = Propiedades.obtenerPropiedades();
			String lista_precios = propiedades.getProperty("sql_descomprimido");
			
			String linea = new String();
			StringBuffer buffer = new StringBuffer();
			BufferedReader input = new BufferedReader(new FileReader(lista_precios));
			while((linea = input.readLine()) != null) {
				buffer.append(linea);
			}			
			input.close();
			String[] querys = buffer.toString().split(";");
			ciclar(querys);
			
			connection.setAutoCommit(true);
			
        } catch(Exception e) { 
             e.printStackTrace();
        }
	}
		
	private void ciclar(String[] querys) throws SQLException {
		Statement statement = connection.createStatement();
		for(int i=0; i < querys.length; i++) {
			//Por las dudas llegue haber una fila vacia al final del script
			if(!querys[i].trim().equals("")) {
				//System.out.println(querys[i] + ";");
				statement.executeUpdate(querys[i] + ";");
			}
			if(i%20000 == 0) {
				connection.commit();
				statement.clearBatch();
			}
		}
		connection.commit();
		statement.close();
	}

}